<?php
class M_Region extends Apps_Db_Table_Abstract {
    protected $_name = 'region';

    //show home with hotel
    public function listRegionByCountryId($countryId) {
        $q = $this->_db
                  ->select()
                  ->from('region', null)
                  ->join('country', 'country.id = region.country_id', null)
                  ->where('country.id =? ', $countryId)
                  ->where('region.status =? ',  1)
                  ->group('region.id')
                  ->columns(array('id' => 'region.id'));

        if($this->getLang() == 'en') {
            $q->columns(array('name' => 'region.en_name'));
        } else $q->columns(array('name' => 'region.name'));

        return $q;
    }
    //show home with hotel
    public function listRegionHotelByCountryId($countryId) {
        $q = $this->_db
                  ->select()
                  ->from('region', null)
                  ->joinleft('hotel', 'hotel.region_id = region.id', null)
                  ->join('country', 'country.id = region.country_id', null)
                  ->where('country.id =? ', $countryId)
                  ->where('region.status =? ',  1)
                  ->group('region.id')
                  ->columns(array('id' => 'region.id'));

        if($this->getLang() == 'en') {
            $q->columns(array('name' => 'region.en_name'));
        } else $q->columns(array('name' => 'region.name'));

        return $q;
    }
    
    //list region by area id
    public function listRegionByAreaId($areaId) {
        $q = $this->_db
                  ->select()
                  ->from('region', null)
                  ->join('area', 'area.id = region.area_id', null)
                  ->join('article', 'article.reference_id = region.id', null)
                  ->where('article.reference_type =? ', M_DbTable_Article::REFERENCE_TYPE_REGION)
                  ->where('area.id =? ', $areaId)
                  ->where('region.status =? ',  1)
                  ->group('region.id')
                  ->columns(array('id' => 'region.id'));

        if($this->getLang() == 'en') {
            $q->columns(array('name' 	=> 'region.en_name', 
            				  'title'	=> 'article.en_title',
            				  'detail'	=> 'article.en_detail'));
        } else {
        	$q->columns(array('name' 	=> 'region.name', 
            				  'title'	=> 'article.title',
           				      'detail'	=> 'article.detail'));
        }

        return $q;
    }
    
    //list region hotel by area id
    public function listRegionHotelByAreaId($areaId) {
        $q = $this->_db
                  ->select()
                  ->from('region', null)
                  ->joinleft('hotel', 'hotel.region_id = region.id', null)
                  ->join('area', 'area.id = region.area_id', null)
                  ->where('area.id =? ', $areaId)
                  ->where('region.status =? ',  1)
                  ->group('region.id')
                  ->columns(array('id' => 'region.id'));

        if($this->getLang() == 'en') {
            $q->columns(array('name' 	=> 'region.en_name'));
        } else {
        	$q->columns(array('name' 	=> 'region.name'));
        }
        return $q;
    }

    public function getRegionById($id) {
        $q = $this->_db
                  ->select()
                  ->from($this->_name, null)
                  ->where('region.id =? ', $id)
                  ->columns(array('id'         => 'region.id',
                                  'country_id' => 'region.country_id'));

        if($this->getLang() == 'en') {
            $q->columns(array('name' => 'region.en_name'));
        } else $q->columns(array('name' => 'region.name'));

        return $q;
    }
}